# 登录注册dao层
from dao import db


# 数据库查验用户名和密码
def dao_login_default(userName, passWord):
    sql = 'select uuid,email,userName,avatar from user where userName = binary "%s" and passWord = binary "%s"' % (userName, passWord)
    data = db.session.execute(sql).fetchone()
    if data is None:
        return False
    field_li = ['uuid', 'email', 'userName', 'avatar']
    result = dict(zip(field_li, data))
    return result


# 数据库查验邮箱和密码
def dao_login_email(email, passWord):
    sql = 'select uuid,email,userName,avatar from user where email = binary "%s" and passWord = binary "%s"' % (email, passWord)
    data = db.session.execute(sql).fetchone()
    if data is None:
        return False
    field_li = ['uuid', 'email', 'userName', 'avatar']
    result = dict(zip(field_li, data))
    return result


# 数据库查验邮箱验证码是否正确
def dao_login_verificationCode(email, captcha):
    sql = 'select * from emailCaptcha where email = binary "%s" and captcha = "%s"' % (email, captcha)
    data = db.session.execute(sql).fetchone()
    if data is None:
        return False
    else:
        return True


# 对验证码进行检验后，用户信息存入数据库
def dao_login_register(email, userName, passWord):
    sql_1 = 'select max(uuid) from user'
    data = db.session.execute(sql_1).fetchone()
    if data is None:
        uid = 100000
    else:
        field_li = ['uuid']
        result = dict(zip(field_li, data))
        uid = result['uuid'] + 1

    default_avatar = "http://43.140.221.237:9999/imgs/avtar/defaultAvatar.jpg"

    sql_2 = 'insert into user (uuid, email, userName, passWord, createDate, avatar) values ' \
            '(%d, "%s", "%s", "%s", now(), "%s")' % (uid, email, userName, passWord, default_avatar)
    try:
        db.session.execute(sql_2)
        db.session.commit()
    except Exception as e:
        db.session.rollback()
        return False
    else:
        return True


# 找回密码，更新密码
def dao_login_forgotPassword(email, newPassword):
    sql = 'update user set passWord = binary "%s" where email = binary "%s"' % (newPassword, email)
    try:
        db.session.execute(sql)
        db.session.commit()
    except Exception as e:
        db.session.rollback()
        return False
    else:
        return True


# 判断是否已有验证码
def dao_login_captcha(email):
    sql = 'select * from emailCaptcha where email = binary "%s"' % email
    data = db.session.execute(sql).fetchone()
    if data is not None:
        return True
    else:
        return False


# 验证码发送功能，将验证码与邮箱存入数据库
def dao_login_sendCaptcha(email, captcha):
    sql = 'insert into emailCaptcha (email, captchaDate, captcha) values ("%s", now(), "%s")' % (email, captcha)
    try:
        db.session.execute(sql)
        db.session.commit()
    except Exception as e:
        db.session.rollback()
        return False
    else:
        return True
